USE [efy-mtl-v1]
------------------------------------------------------------------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].mTL_ReminderGetAll') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].mTL_ReminderGetAll
GO
CREATE PROCEDURE [dbo].mTL_ReminderGetAll
	@staff_id			Varchar(50)					-- Id NSD hien thoi
WITH ENCRYPTION
AS
	SET NOCOUNT ON
		Declare @str_sql nvarchar(4000)
		Create Table #T_ALL_RECORD(P_ID int IDENTITY (1,1)
									,STAFF_ID nvarchar(50)
									,TRAINING_IDEA INT    
									,TRAINING_REFUSED INT
									,LEARNING_REFUSED INT
									,TRAINING_APPROVE INT
									,LEARNING_APPROVE INT)
		Set @str_sql = ' Insert into #T_ALL_RECORD(STAFF_ID) values(' + char(39) + @staff_id + char(39) +')'
		Print @str_sql
		Exec (@str_sql)
		--Bai giang cho cho y kien
		Update #T_ALL_RECORD set TRAINING_IDEA = (select COUNT(*) 
			from T_mTL_TRAINING_DOC_STAFF_PROCESS 
			where FK_STAFF = @staff_id AND C_STATUS ='CHO_CHO_Y_KIEN')
		-- Bai giang bi lanh dao tu choi
		Update #T_ALL_RECORD set TRAINING_REFUSED = (select COUNT(*) 
			from T_mTL_TRAINING_DOC 
			where FK_CREATER = @staff_id AND C_STATUS ='KHONG_DUYET')
		-- Tu lieu bai hoc bi lanh dao tu choi
		Update #T_ALL_RECORD set LEARNING_REFUSED = (select COUNT(*) 
			from T_mTL_LEARNING_DOC 
			where FK_CREATER = @staff_id AND C_STATUS ='KHONG_DUYET')
		-- Bai giang cho duyet
		Update #T_ALL_RECORD set TRAINING_APPROVE = (select COUNT(*) 
			from T_mTL_TRAINING_DOC 
			where FK_LEADER_STAFF like @staff_id AND C_STATUS ='CHO_DUYET')
		-- Tu lieu bai hoc cho duyet
		Update #T_ALL_RECORD set LEARNING_APPROVE = (select COUNT(*) 
			from T_mTL_LEARNING_DOC 
			where FK_LEADER_STAFF like @staff_id AND C_STATUS ='CHO_DUYET')
		SELECT * FROM #T_ALL_RECORD
	SET NOCOUNT OFF
Return 0
GO
/*
Exec mTL_ReminderGetAll 'B0C2DDF7-A129-48A4-822D-D8EAC6FD7D01'
*/